package test01.demo04.daoImpl;

import test01.demo02.userOperation.tools.Const;
import test01.demo03.ConnectionTool;
import test01.demo04.dao.PersonDao;
import test01.demo04.pojo.Person;

import java.sql.*;
import java.util.*;

public class PersonDaoImpl implements PersonDao {

    Connection conn = null;

    static{
        try {
            Class.forName(Const.DRIVER);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 同时添加多个Person对象数据
     * @param people
     */
    @Override
    public boolean insert(List<Person> people) {

        PreparedStatement ps_insert = null;
        boolean result = true;

        try {
            conn = ConnectionTool.getDefaultConn();

            conn.setAutoCommit(false);

            ps_insert = conn.prepareStatement(
                    "insert into people values(?,?,?,?,?,?)"
            );

            for (Person person : people) {
                ps_insert.setString(1, person.getPersonId());
                ps_insert.setString(2, person.getPersonName());
                ps_insert.setString(3, person.getDegree());
                ps_insert.setString(4, person.getBirth());
                ps_insert.setDouble(5,person.getSal());
                ps_insert.setString(6,"0");
                ps_insert.addBatch();
            }

            int[] insertCount = ps_insert.executeBatch();

            if(insertCount.length==people.size() && Arrays.stream(insertCount).allMatch(num->num==1)){
                conn.commit();
            }else{
                result = false;
                conn.rollback();
            }

        } catch (SQLException e) {

            try {
                result = false;
                conn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            finally {
                ConnectionTool.closed(conn,ps_insert);
            }
        }

        return result;

    }

    /**
     * 根据人员编号删除多个数据
     * @param ids
     */
    @Override
    public boolean delete(String[] ids) {
        PreparedStatement ps_delete = null;
        boolean result = true;

        try {
            conn = ConnectionTool.getDefaultConn();
            conn.setAutoCommit(false);
            ps_delete = conn.prepareStatement(
                    "delete from people where personId=?"
            );
            for (String id : ids) {
                ps_delete.setString(1,id);
                ps_delete.addBatch();
            }
            int[] results = ps_delete.executeBatch();

            if(results.length== ids.length && Arrays.stream(results).allMatch(num->num>0)){
                conn.commit();
            }else{
                result = false;
                conn.rollback();
            }


        } catch (SQLException e) {
            try {
                result = false;
                conn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            finally {
                ConnectionTool.closed(conn,ps_delete);
            }
        }

        return result;

    }


    @Override
    public int update(Person people) {

        PreparedStatement ps_update = null;
        int result = 0;

        try {
            conn = ConnectionTool.getDefaultConn();
            ps_update = conn.prepareStatement(
                    "update people set personName=?,degree=?,birth=?,sal=? where personId=?"
            );
            ps_update.setString(1,people.getPersonName());
            ps_update.setString(2,people.getDegree());
            ps_update.setString(3,people.getBirth());
            ps_update.setDouble(4,people.getSal());
            ps_update.setString(5, people.getPersonId());

            result = ps_update.executeUpdate();


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return result;
    }


    /**
     * 查询所有人员，并将返回值存储在List中
     * @return
     */
    @Override
    public List<Map<String, Object>> getAll() {
        PreparedStatement ps_getAll = null;
        List<Map<String,Object>> list = new ArrayList<>();
        Person people = null;


        try {
            conn = ConnectionTool.getDefaultConn();
            ps_getAll = conn.prepareStatement(
                    "select * from people"
            );

            ResultSet resultSet = ps_getAll.executeQuery();

            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //获取每条数据有多少列，也就是一个Map中有多少个键值对
            int columnCount = rsmd.getColumnCount();

            //每执行一次while，就是一条数据
            while (resultSet.next()) {
                //每一条数据就是一个Map
                Map<String, Object> map = new HashMap<>();

                //需要对从第一列开始到columnCount列的键值对进行封装
                for (int i = 1;i <= columnCount; i++){
                    map.put(rsmd.getColumnName(i),resultSet.getObject(i));
                }
                list.add(map);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return list;
    }

    @Override
    public List<Map<String, Object>> getPersonForPage(int pagesize, int pageOrder) {
        PreparedStatement ps_getPersonForPage = null;
        List<Map<String,Object>> list = new ArrayList<>();



        try {
            conn = ConnectionTool.getDefaultConn();
            ps_getPersonForPage = conn.prepareStatement(
                    "select * from people limit ? offset ?"
            );

            ps_getPersonForPage.setInt(1,pagesize);
            ps_getPersonForPage.setInt(2,(pageOrder-1)*pagesize);


            ResultSet resultSet = ps_getPersonForPage.executeQuery();

            //获取结果集的元数据
            ResultSetMetaData rsmd = resultSet.getMetaData();
            //获取每条数据有多少列，也就是一个Map中有多少个键值对
            int columnCount = rsmd.getColumnCount();

            //每执行一次while，就是一条数据
            while (resultSet.next()) {
                //每一条数据就是一个Map
                Map<String, Object> map = new HashMap<>();

                //需要对从第一列开始到columnCount列的键值对进行封装
                for (int i = 1;i <= columnCount; i++){
                    map.put(rsmd.getColumnName(i),resultSet.getObject(i));
                }
                list.add(map);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return list;
    }

    @Override
    public Person getPersonById(String id) {
        PreparedStatement ps_getPersonId = null;
        Person people = null;
        try {
            conn = ConnectionTool.getDefaultConn();
            ps_getPersonId = conn.prepareStatement(
                    "select * from people where personId=?"
            );
            ps_getPersonId.setString(1,id);
            ResultSet resultSet = ps_getPersonId.executeQuery();

            if (resultSet.next()){
                people = new Person(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getDouble(5)
                );
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return people;
    }
}
